﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;

namespace 工厂模式
{
    public class MssqlFactory : IDBFactory
    {
        private string _strConnectionString=string.Empty;

        public MssqlFactory(string strConnectionString)
        {
            _strConnectionString = strConnectionString;
        }

        #region 根据连接字符串获得数据库连接对象
        public DbConnection GetConnection(string strConnectionString)
        {
            SqlConnection _conn = new SqlConnection(strConnectionString);
            if (_conn.State == ConnectionState.Closed)
            {
                _conn.Open();
            }
            return _conn;
        }
        #endregion

        #region 获得数据库连接对象
        public DbConnection GetConnection()
        {
            SqlConnection _conn = new SqlConnection(_strConnectionString);
            if (_conn.State == ConnectionState.Closed)
            {
                _conn.Open();
            }
            return _conn;
        }
        #endregion

        #region 根据名称和值或得数据库参数对象
        public DbParameter GetParameter(string name, object value)
        {
            SqlParameter parm = new SqlParameter(name, value);
            return parm;
        }
        #endregion

        #region 根数名称和类型获得数据库参数对象
        public DbParameter GetParameter(string name, DbType type)
        {
            SqlParameter _parm = new SqlParameter(name, type);
            return _parm;
        }
        #endregion

        #region 根据名称、类型和值获得数据库参数对象
        public DbParameter GetParameter(string name, DbType type, object value)
        {
            SqlParameter _parm = new SqlParameter(name, type);
            _parm.Value = value;
            return _parm;
        }
        #endregion

        #region 根据连接对象、sql语句获得数据库命令对象
        public DbCommand GetCommand(DbConnection conn, string strSql, params DbParameter[] parms)
        {
            SqlConnection _conn = conn as SqlConnection;
            SqlCommand _cmd = new SqlCommand(strSql, _conn);
            SqlParameter[] _parms = Array.ConvertAll(parms, parm => { return parm as SqlParameter; });
            _cmd.Parameters.AddRange(_parms);
            return _cmd;
        }
        #endregion

        #region 根据sql语句获得数据库连接对象
        public DbCommand GetCommand(string strSql, params DbParameter[] parms)
        {
            SqlConnection _conn = GetConnection() as SqlConnection;
            SqlCommand _cmd = new SqlCommand(strSql,_conn);
            SqlParameter[] _parms = Array.ConvertAll(parms, parm => { return parm as SqlParameter; });
            _cmd.Parameters.AddRange(_parms);
            return _cmd;
        }
        #endregion

        #region 执行非查询操作
        public int ExecuteNonQuery(string strSql, params DbParameter[] parms)
        {
            SqlConnection _conn = GetConnection() as SqlConnection;
            try
            {
                SqlCommand _cmd = new SqlCommand(strSql, _conn);
                SqlParameter[] _parms = Array.ConvertAll(parms, parm => { return parm as SqlParameter; });
                _cmd.Parameters.AddRange(_parms);
                int intRes = _cmd.ExecuteNonQuery();
                return intRes;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                _conn.Close();
            }
        }
        #endregion

        #region 执行非查询操作
        public int ExecuteNonQuery(DbConnection conn, string strSql, params DbParameter[] parms)
        {
            SqlConnection _conn = conn as SqlConnection;
            try
            {
                SqlCommand _cmd = new SqlCommand(strSql, _conn);
                SqlParameter[] _parms = Array.ConvertAll(parms, parm => { return parm as SqlParameter; });
                _cmd.Parameters.AddRange(_parms);
                int intRes = _cmd.ExecuteNonQuery();
                return intRes;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                _conn.Close();
            }
        }
        #endregion

        #region 执行非查询操作
        public int ExecuteNonQuery(DbTransaction tran, string strSql, params DbParameter[] parms)
        {
            SqlTransaction _tran = tran as SqlTransaction;
            try
            {
                SqlCommand _cmd = new SqlCommand(strSql, _tran.Connection);
                SqlParameter[] _parms = Array.ConvertAll(parms, parm => { return parm as SqlParameter; });
                _cmd.Parameters.AddRange(_parms);
                int intRes = _cmd.ExecuteNonQuery();
                return intRes;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 获得DbDataReader对象
        public DbDataReader ExecuteDataReader(string strSql, params DbParameter[] parms)
        {
            try
            {
                SqlConnection _conn =GetConnection() as SqlConnection;
                SqlCommand _cmd = new SqlCommand(strSql, _conn);
                SqlParameter[] _parms = Array.ConvertAll(parms, parm => { return parm as SqlParameter; });
                SqlDataReader _sdr = _cmd.ExecuteReader();
                return _sdr;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 获得DbDataReader对象
        public DbDataReader ExecuteDataReader(DbConnection conn, string strSql, params DbParameter[] parms)
        {
            try
            {
                SqlConnection _conn = conn as SqlConnection;
                SqlCommand _cmd = new SqlCommand(strSql, _conn);
                SqlParameter[] _parms = Array.ConvertAll(parms, parm => { return parm as SqlParameter; });
                SqlDataReader _sdr = _cmd.ExecuteReader();
                return _sdr;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 获得DbDataReader对象
        public DbDataReader ExecuteDataReader(DbTransaction tran, string strSql, params DbParameter[] parms)
        {
            try
            {
                SqlConnection _conn = tran.Connection as SqlConnection;
                SqlCommand _cmd = new SqlCommand(strSql, _conn);
                SqlParameter[] _parms = Array.ConvertAll(parms, parm => { return parm as SqlParameter; });
                SqlDataReader _sdr = _cmd.ExecuteReader();
                return _sdr;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 获得DataTable
        public DataTable GetDataTable(string strSql, params DbParameter[] parms)
        {
            try
            {
                DataTable dt = new DataTable();
                SqlConnection _conn = GetConnection() as SqlConnection;
                SqlCommand _cmd = new SqlCommand(strSql, _conn);
                SqlParameter[] _parms = Array.ConvertAll(parms, parm => { return parm as SqlParameter; });
                _cmd.Parameters.AddRange(_parms);
                SqlDataAdapter _ada = new SqlDataAdapter(_cmd);
                _ada.Fill(dt);
                return dt;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 获得DataTable
        public DataTable GetDataTable(DbConnection conn, string strSql, params DbParameter[] parms)
        {
            try
            {
                DataTable dt = new DataTable();
                SqlConnection _conn = conn as SqlConnection;
                SqlCommand _cmd = new SqlCommand(strSql, _conn);
                SqlParameter[] _parms = Array.ConvertAll(parms, parm => { return parm as SqlParameter; });
                _cmd.Parameters.AddRange(_parms);
                SqlDataAdapter _ada = new SqlDataAdapter(_cmd);
                _ada.Fill(dt);
                return dt;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 获得DataTable
        public DataTable GetDataTable(DbTransaction tran, string strSql, params DbParameter[] parms)
        {
            try
            {
                DataTable dt = new DataTable();
                SqlConnection _conn=tran.Connection as SqlConnection;
                SqlCommand _cmd = new SqlCommand(strSql, _conn);
                SqlParameter[] _parms = Array.ConvertAll(parms, parm => { return parm as SqlParameter; });
                _cmd.Parameters.AddRange(_parms);
                SqlDataAdapter _ada = new SqlDataAdapter(_cmd);
                _ada.Fill(dt);
                return dt;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        #endregion
    }
}
